package com.ucom;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.util.LinkedList;

public class Query13
{	private Connection dbConnection;
	private String query;
	private ResultSet rslt;
	private Statement stmtGetListEntries;

	public Query13 ()
	{	try 
		{	Class.forName("org.apache.derby.jdbc.ClientDriver");
			dbConnection = DriverManager.getConnection("jdbc:derby://localhost:1527/ucomm");
			stmtGetListEntries = dbConnection.createStatement(
					ResultSet.TYPE_SCROLL_INSENSITIVE,
					ResultSet.CONCUR_READ_ONLY);

		} catch (ClassNotFoundException cnfe) {
			System.out.println("Error in creating Database conncetion");
			System.out.println(cnfe.getMessage());

		} catch (SQLException sqle) {
			System.out.println("Error in creating Database conncetion");
			System.out.println(sqle.getMessage());
		}

	}

	public double query13() 
	{	double result=-1;
		try
		{		query = "SELECT NUMFEMALES/NUMMALES AS RATIO"
					+	" FROM ( SELECT CAST(COUNT(*) AS FLOAT) AS NUMMALES"
							+" 	FROM DEPT_INFO DI, USER_INFO UI"
							+"	WHERE UI.SEX = 'M'"
							+" 	and DI.DEPT_ID = UI.DEPT_ID"
							+" 	AND DI.DEPT_NAME = 'Computer Science'"
							+" 	HAVING COUNT (*)>0 ) MALES,"
							+" ( SELECT  CAST(COUNT(*) AS FLOAT) AS  NUMFEMALES"
							+" FROM DEPT_INFO DI, USER_INFO UI"
							+" WHERE UI.SEX = 'F'"
							+" AND DI.DEPT_ID = UI.DEPT_ID"
							+" AND DI.DEPT_NAME = 'Computer Science'"
							+" HAVING COUNT (*)>0 ) FEMALES";

				rslt = stmtGetListEntries.executeQuery(query);
				rslt.next();
				result=rslt.getDouble(1);
				System.out.print("--------Query 13 result--------------------\n");
				System.out.println(result);
				
				rslt.close();
		} 
		catch (SQLException sqle) 
		{	System.out.println("Error in getting transaction details");
			System.out.println(sqle.getMessage());
		} 
		catch (Exception e) 
		{	e.printStackTrace();
		}
		return result;
	}

}
